package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MCostDetail;
import org.compiere.model.MInventory;
import org.compiere.model.MInventoryLine;
import org.compiere.model.MLocator;
import org.compiere.model.MOrg;
import org.compiere.model.MProduct;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;

public class MM_Form_443 extends SvrProcess {
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	int p_M_Inventory_ID;
	/** */
	String file;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("M_Inventory_ID")) 
				p_M_Inventory_ID  = para[i].getParameterAsInt();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

	}

	@Override
	protected String doIt() throws Exception {
		file = Util.localFilePath + "Form_443_.xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		//Set Organization name
		R = sheet.getRow(9);
		C = R.getCell(0);
		C.setCellValue(MOrg.get(m_ctx,Env.getAD_Org_ID(m_ctx)).getName());
		//Style for cells
		CellStyle freeStyle = wb.createCellStyle();
		freeStyle.setBorderBottom((short)1);
		freeStyle.setBorderTop((short)1);
		freeStyle.setBorderLeft((short)1);
		freeStyle.setBorderRight((short)1);
		freeStyle.setFont(sheet.getRow(9).getCell(0).getCellStyle().getFont(wb));
		freeStyle.setWrapText(true);
		freeStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		
		CellStyle indexNumberStyle = wb.createCellStyle();
		indexNumberStyle.cloneStyleFrom(freeStyle);
		indexNumberStyle.setAlignment(CellStyle.ALIGN_CENTER);
		
		HSSFDataFormat cf = wb.createDataFormat();
		CellStyle AmtCellStyle = wb.createCellStyle();
		AmtCellStyle.cloneStyleFrom(freeStyle);
		AmtCellStyle.setDataFormat(cf.getFormat("#,##0.00"));
		
		
		//Set Current date
		R = sheet.getRow(24);
		C = R.getCell(29);
		C.setCellValue(new SimpleDateFormat("dd").format(new Timestamp(System.currentTimeMillis())));
		C = R.getCell(32);
		C.setCellValue(new SimpleDateFormat("MMMMM").format(new Timestamp(System.currentTimeMillis())));
		//Set DocumentNo
		R = sheet.getRow(30);
		C = R.getCell(10);
		MInventory inventory = new MInventory(m_ctx, p_M_Inventory_ID, get_TrxName()); 
		C.setCellValue(inventory.getDocumentNo());
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int row = 49;
		String sql = "SELECT M_InventoryLine_ID FROM M_InventoryLine WHERE AD_Client_ID = " 
				+ Env.getAD_Client_ID(m_ctx)
				+ " AND AD_Org_ID = "+Env.getAD_Org_ID(m_ctx)
				+ " AND M_Inventory_ID = "+ p_M_Inventory_ID
				+ " ORDER BY M_Product_ID";
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			int iM_InventoryLine_ID = 0,nn = 0;
			while(rs.next())
			{
				nn++; row++;
				sheet.shiftRows(row, row+1, 1);
				iM_InventoryLine_ID = rs.getInt(1);
				MInventoryLine inventoryLine = new MInventoryLine(m_ctx, iM_InventoryLine_ID, get_TrxName());
				//col1 0-1
				R = sheet.getRow(row);
				for(int i=0; i<=41;i++)
				{
					C = R.createCell(i);
					C.setCellStyle(freeStyle);
				}

				C = R.getCell(0);
				C.setCellValue(nn);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 1));
				C.setCellStyle(indexNumberStyle);
				//
				MProduct iproduct = new MProduct(m_ctx, inventoryLine.getM_Product_ID(), get_TrxName());
				//col2 2-5
				C = R.getCell(2);
				C.setCellValue(iproduct.getValue());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 2, 5));
				C.setCellStyle(indexNumberStyle);
				//col3 6-11
				C = R.getCell(6);
				C.setCellValue(iproduct.getName());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 6, 11));
				//col4 12-15
				C = R.getCell(12);
				C.setCellValue("01.01.12");
				sheet.addMergedRegion(new CellRangeAddress(row, row, 12, 15));
				C.setCellStyle(indexNumberStyle);
				//col5 16-19
				C = R.getCell(16);
				C.setCellValue(1);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 16, 19));
				C.setCellStyle(indexNumberStyle);
				//col6 20-23
				C = R.getCell(20);
				C.setCellValue(inventoryLine.getQtyInternalUse().intValue());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 20, 23));
				//col7 24-27
				C = R.getCell(24);
					PreparedStatement pstmt1 = null;
					ResultSet rs1 = null;
					MCostDetail icostdetail = null;
					String sql1 = "SELECT M_CostDetail_ID FROM M_CostDetail" 
								+ " WHERE AD_Client_ID = " + Env.getAD_Client_ID(m_ctx) 
								+ " AND AD_Org_ID = " + Env.getAD_Org_ID(m_ctx)
								+ " AND M_Product_ID = " + inventoryLine.getM_Product_ID()
								+ " AND M_AttributeSetInstance_ID = " + inventoryLine.getM_AttributeSetInstance_ID()
								+ " AND M_InventoryLine_ID = " + inventoryLine.getM_InventoryLine_ID();
					try
					{	
						pstmt1 = DB.prepareStatement(sql1.toString(), get_TrxName());
						rs1 = pstmt1.executeQuery();
						int iM_CostDetail_ID = 0;
						while(rs1.next())
						{
							iM_CostDetail_ID = rs1.getInt(1);
							icostdetail = new MCostDetail(m_ctx, iM_CostDetail_ID, get_TrxName());
						}
						
					}
					finally
					{
						DB.close(rs1, pstmt1);
					}
					
				C.setCellValue(icostdetail.getCurrentCostPrice().doubleValue());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 24, 27));
				C.setCellStyle(AmtCellStyle);
				//col8 28-33
				C = R.getCell(28);
				C.setCellValue(icostdetail.getAmt().negate().doubleValue());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 28, 33));
				C.setCellStyle(AmtCellStyle);
				//col9 34-37
				C = R.getCell(34);
				C.setCellValue(inventoryLine.getDescription());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 34, 37));
				
				int len = (inventoryLine.getDescription() == null ? 0:inventoryLine.getDescription().length());
				int height = 0; 
				if(len > 0){
				 int diff = len / 6;
				 if(diff > 0){
					 height =diff*255;
					 R.setHeight((short)height);
				 }
				}
				//col10 38-41
				sheet.addMergedRegion(new CellRangeAddress(row, row, 38, 41));
				MLocator iLocator = new MLocator(m_ctx, iproduct.getM_Locator_ID(), get_TrxName());
				C = R.getCell(38);
				C.setCellValue(iLocator.getValue());
			}
			
		}
		catch(Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally
		{
			DB.close(rs, pstmt);
		}
		
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(file);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
					Util.launchFile(new File(file));
				//
		
		return Msg.translate(m_ctx, "Success");
	}

}
